
from futu import *
import pandas as pd
from sqlalchemy import create_engine
import time
import  logging

logging.basicConfig(filename='../logs/futu.log',format='[%(asctime)s-%(filename)s-%(levelname)s:%(message)s]', level = logging.DEBUG,filemode='a',datefmt='%Y-%m-%d%I:%M:%S %p')

tablename = "accinfo"

SysConfig.set_proto_fmt(ProtoFMT.Json)
context_hk = OpenHKTradeContext(host='127.0.0.1', port=11111)


context_us =OpenUSTradeContext(host='127.0.0.1', port=11111, is_encrypt=None, security_firm=SecurityFirm.FUTUSECURITIES)


'''
trd_ctx.unlock_trade(password_md5='d6d1d2b50655a964810ba5592c9200a5', is_unlock=True)
'''

assethk = context_hk.accinfo_query(trd_env=TrdEnv.REAL, acc_id=0, acc_index=0, refresh_cache=False, currency=Currency.HKD)

assetus = context_us.accinfo_query(trd_env=TrdEnv.REAL, acc_id=0, acc_index=0, refresh_cache=False, currency=Currency.USD)


context_hk.close()  # 关闭对象，防止连接条数用尽

dfhk =pd.DataFrame(assethk[1])
dfhk['Market'] = "HK"
dfhk['addtime'] =  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) 

dfus =pd.DataFrame(assetus[1])
dfus['Market'] = "US"
dfus['addtime'] =  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) 


logging.info('-------'*10)

conn = create_engine('mysql+pymysql://futu:futu@localhost:3306/FutuDB',encoding='utf8')


pd.io.sql.to_sql(dfhk,tablename, conn,if_exists='replace',index=False)

pd.io.sql.to_sql(dfus,tablename, conn,if_exists='append',index=False)

logging.info('save data to mysql')